package org.termine.dao;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import javax.faces.bean.ApplicationScoped;
import javax.faces.bean.ManagedBean;

import org.termine.entity.Location;

@ManagedBean
@ApplicationScoped
public class LocationDAO extends BaseDAO implements Serializable {

	private static final long serialVersionUID = 7076097669104996671L;
	
	public Integer getLocationForProvider(Connection connection, Integer providerId) throws Exception {
		PreparedStatement statement = null;
		String statementString = "select L_ID from PERSONAL_DATA pd where pd.P_ID = ?";
		try {
			statement = connection.prepareStatement(statementString);
			statement.setInt(1, providerId);
			ResultSet resultSet = statement.executeQuery();
			if (resultSet.next()) {
				return new Integer(resultSet.getInt("L_ID"));
			} else {
				return null;
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
		}
	}

	public Location getLocation(Connection connection, Integer locationId) throws Exception {
		PreparedStatement statement = null;
		String statementString = "select L_ID, L_COUNTRY, L_CITY, L_ZIP, L_STREET, db2gse.ST_X "
				+ "(L_POSITION) X, db2gse.ST_Y (L_POSITION) Y from LOCATION where L_ID = ?";
		try {
			statement = connection.prepareStatement(statementString);
			statement.setInt(1, locationId);
			ResultSet resultSet = statement.executeQuery();

			if (resultSet.next()) {
				Location result = new Location();
				Integer integer;
				String string;
				double decimal;

				integer = resultSet.getInt("L_ID");
				if (!resultSet.wasNull()) {
					result.setLocation_id(integer);
				}
				string = resultSet.getString("L_COUNTRY");
				if (!resultSet.wasNull()) {
					result.setCountry(string);
				}
				string = resultSet.getString("L_CITY");
				if (!resultSet.wasNull()) {
					result.setCity(string);
				}
				string = resultSet.getString("L_ZIP");
				if (!resultSet.wasNull()) {
					result.setZip(string);
				}
				string = resultSet.getString("L_STREET");
				if (!resultSet.wasNull()) {
					result.setStreet(string);
				}
				decimal = resultSet.getDouble("X");
				if (!resultSet.wasNull()) {
					result.setLatitude(decimal);
				}
				decimal = resultSet.getDouble("Y");
				if (!resultSet.wasNull()) {
					result.setLongitude(decimal);
				}
				return result;
			} else {
				return null;
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
		}
	}

	public Integer getLocationId(Connection connection, Location location) throws Exception {
		PreparedStatement statement = null;
		String statementString = "select L_ID from LOCATION where L_COUNTRY = ? and L_CITY = ? and "
				+ "L_ZIP = ? and L_STREET = ?";
		try {
			statement = connection.prepareStatement(statementString);
			statement.setString(1, location.getCountry());
			statement.setString(2, location.getCity());
			statement.setString(3, location.getZip());
			statement.setString(4, location.getStreet());
			ResultSet resultSet = statement.executeQuery();
			if (resultSet.next()) {
				return new Integer(resultSet.getInt("L_ID"));
			} else {
				return null;
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
		}
	}

	public void addLocation(Connection connection, Location location) throws Exception {
		PreparedStatement statement = null;
		String statementString = "insert into LOCATION (L_COUNTRY, L_CITY, L_ZIP, L_STREET, L_POSITION) "
				+ "values (?,?,?,?, db2gse.ST_Point(cast(? as double),cast(? as double),1003))";
		try {
			if (getLocationId(connection, location) == null) {
				statement = connection.prepareStatement(statementString);
				statement.setString(1, location.getCountry());
				statement.setString(2, location.getCity());
				statement.setString(3, location.getZip());
				statement.setString(4, location.getStreet());
				statement.setDouble(5, location.getLatitude());
				statement.setDouble(6, location.getLongitude());
				statement.executeUpdate();
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
		}
	}

	public double getDistanceToLocation(Connection connection, Integer locationId,
			double longitude, double latitude) throws Exception {
		PreparedStatement statement = null;
		String statementString = "SELECT DECIMAL(db2gse.ST_Distance(l.L_POSITION, db2gse.ST_Point(cast(? as double),cast(? as double),1003),'KILOMETER' ),6,1) as X from LOCATION l where l.L_ID = ?";
		try {
			statement = connection.prepareStatement(statementString);
			statement.setDouble(1, latitude);
			statement.setDouble(2, longitude);
			statement.setInt(3, locationId);
			ResultSet resultSet = statement.executeQuery();
			resultSet.next();
			return resultSet.getDouble("X");
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
		}
	}
}
